use [Daco]
go
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.GetUserPermission') AND type in (N'P')))
	DROP PROCEDURE dbo.GetUserPermission
GO
CREATE PROCEDURE dbo.GetUserPermission
	@Module	VARCHAR(50),
	@Name	VARCHAR(50),
	@UserName	NVARCHAR(50)
AS
BEGIN
	;WITH UserInRoles(RoleID) AS
	(
		SELECT     r.RoleID
		FROM         Roles AS r INNER JOIN
							  UsersInRoles AS ur ON r.RoleID = ur.RoleID INNER JOIN
							  Users AS u ON ur.UserID = u.UserID
		WHERE     (u.LoweredUserName = LOWER(@UserName))
	)
	
	SELECT *
	FROM [Permissions] AS p 
		INNER JOIN UserInRoles AS ur ON ur.RoleID = p.RoleID
	WHERE p.Module = @Module AND p.Name = p.Name
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.GetPages') AND type in (N'P')))
	DROP PROCEDURE dbo.GetPages
GO
CREATE PROCEDURE dbo.GetPages
AS
BEGIN
	SELECT     Module, Name, ISNULL(Description, N'') AS Description, PValue
	FROM         Pages
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.AddPage') AND type in (N'P')))
	DROP PROCEDURE dbo.AddPage
GO
CREATE PROCEDURE dbo.AddPage
	@Module	VARCHAR(50),
	@Name	VARCHAR(50),
	@Permission	INT,
	@Description	NVARCHAR(500)
AS
BEGIN
	IF(EXISTS(SELECT * FROM Pages WHERE Module = @Module AND Name = @Name)) BEGIN
		UPDATE Pages SET [Description] = @Description
		WHERE Module = @Module AND Name = @Name
	END
	ELSE BEGIN
		INSERT INTO Pages(Module, Name, PValue, Description)
		VALUES (@Module, @Name, @Permission, @Description)
	END
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.UpdatePage') AND type in (N'P')))
	DROP PROCEDURE dbo.UpdatePage
GO
CREATE PROCEDURE dbo.UpdatePage
	@Module	VARCHAR(50),
	@Name	VARCHAR(50),
	@Description	NVARCHAR(500)
AS
BEGIN
	UPDATE Pages SET [Description] = @Description
	WHERE Module = @Module AND Name = @Name
END
GO